<?php
	/*
		this file generates a MYSQL CREATE script based on the data saved on the mysql server
		it needs the project id and the json id of the diagram
	*/
	require_once("../functions.php");
	
	if(!isLoggedIn()){
		die("Please login first.");
	}
	
	if(!isset($_GET["id"]) || !isset($_GET["jsonId"])){
		die("No parameters");
	}
	
	date_default_timezone_set("Asia/Manila");
	$componentIds = array();
	$columns = array();
	
	/**************FUNCTIONS*****************/
	
	function appendCode($text1 = "", $text2 = "", $indent=0){
		$tab = "";
		for($i=0; $i<$indent; $i++){
			$tab = $tab . "\t";
		}
		
		return $text1 . $tab . $text2 . "\n";
	}
	
	function parseCustomAttribute($cAttribute){
		$customAttr = explode("|", $cAttribute);
		
		$retVal = array();
		
		for($j=0; $j< count($customAttr); $j++){
			if(strlen(trim($customAttr[$j]))==0){
				continue;
			}
			
			$customItem = explode("#", $customAttr[$j]);
			
			$retVal[$customItem[0]] = $customItem[2];
		}
		
		return $retVal;
	}
	
	function getColumns($componentId, $diagramId, $level=0){
		global $componentIds, $columns;
		
		if($level==0){
			$componentIds = array();
			$columns = array();
		}
		
		$sql = "SELECT DISTINCT c.id, label, componentType, c.custom FROM component c, (SELECT * FROM connection WHERE (component1_id=$componentId OR component2_id=$componentId)) t WHERE (component1_id=id OR component2_id=id) AND NOT(id=$componentId) AND NOT(componentType='entity' OR componentType='wEntity' OR componentType='mAttribute') AND diagram_id=$diagramId";
		$result = mysql_query($sql) or die("addColumn: " + mysql_error);
		
		if(mysql_num_rows($result)>0){
			for($i=0;$i<mysql_num_rows($result); $i++){
				$row = mysql_fetch_array($result);
				
				if(strcmp($row["componentType"],"kAttribute")==0){
					$columns[count($columns)] = $row["id"];
				}else if(in_array($row["id"], $componentIds)){
					$sql = "select * from component where id=$componentId";
					$result2 = mysql_query($sql) or die("Print component: " . mysql_error());
					
					while($row = mysql_fetch_array($result2)){
						$columns[count($columns)] = $row["id"];
					}
					
					mysql_free_result($result2);
				}else{
					$componentIds[count($componentIds)] = $row["id"];
					getColumns($row["id"],$diagramId,$level+1);
				}
			}
		}else{
			$sql = "select * from component where id=$componentId";
			$result2 = mysql_query($sql) or die("Print component: " . mysql_error());
			
			while($row = mysql_fetch_array($result2)){
				$columns[count($columns)] = $row["id"];
			}
			
			mysql_free_result($result2);
		}
		mysql_free_result($result);
	}
	
	/**************END OF FUNCTIONS*****************/
	
	connectToDb();
	
	//get database name
	$sql0 = "SELECT d.id, d.name FROM diagram d, project p WHERE d.jsonId = '$_GET[jsonId]' AND d.project_id=p.id AND p.id=$_GET[id]";
	$dResult = mysql_query($sql0) or die(mysql_error());
	$dRow = mysql_fetch_array($dResult);
	mysql_free_result($dResult);
	$dName = str_replace(" ", "_", $dRow["name"]);
	$content = "DROP SCHEMA IF EXISTS $dName;\n";
	$content = appendCode($content, "CREATE SCHEMA IF NOT EXISTS $dName;");
	$content = appendCode($content, "USE $dName;");
	
	//select tables to be created excluding multivalued attributes and many to many relationship, ordered by the least number of foreign keys it has
	$sql1 = "
	SELECT * FROM component c,
		(SELECT t2.id id2, t2.score + IFNULL(t3.num,0) score FROM 	
			(SELECT c.id,IFNULL(t1.num,0) score FROM component c LEFT JOIN 
				(SELECT component1_id comp, COUNT(cardinality1) num FROM connection WHERE
					(connectionType='relationship' OR connectionType='iRelationship') AND (cardinality1 REGEXP '[^0-9]+') 
				GROUP BY component1_id) t1 ON (c.id=t1.comp)
			) t2 LEFT JOIN 
			(SELECT component2_id comp, COUNT(cardinality2) num FROM connection 
				WHERE(connectionType='relationship' OR connectionType='iRelationship') AND 
				(cardinality2 REGEXP '[^0-9]+') GROUP BY component2_id
			) t3
		ON t2.id=t3.comp) t4
	WHERE c.id=t4.id2 AND (componentType='entity' OR componentType='wEntity') AND diagram_id=$dRow[id] ORDER BY score,id";
	
	$tResult = mysql_query($sql1) or die(mysql_error());
	while($tRow = mysql_fetch_array($tResult)){
		$content = appendCode($content, "\nDROP TABLE IF EXISTS $tRow[name];");
		$content = appendCode($content, "CREATE  TABLE IF NOT EXISTS $tRow[name](");
		
		//select columns excluding multivalued attributes
		getColumns($tRow["id"], $dRow["id"]);
		$cNumRows = count($columns);
		$pk = array();
		$numPk = 0;
		for($i =0; $i<$cNumRows; $i++){
			$sql2 = "select * from component where id=" . $columns[$i];
			$cResult = mysql_query($sql2) or die("select column details: " . mysql_error());
			$cRow = mysql_fetch_array($cResult);
			mysql_free_result($cResult);
			
			$label = str_replace(" ", "_", $cRow["label"]);
			$custom = parseCustomAttribute($cRow["custom"]);
			$isLast = false;
			
			if($i== $cNumRows-1 && count($pk)==0){
				$isLast=true;
			}
			
			if(strcmp($cRow["componentType"], "kAttribute")==0){
				$ai = (strcmp($custom["autoincrement"], "true")==0) ? "AUTO_INCREMENT" : ""; 
				$content = appendCode($content, "$label $custom[datatype] NOT NULL $ai,",1);
				$pk[$numPk++] = $label;
			}else{
				$not_null = (strcmp($custom["not_null"], "true")==0) ? "NOT NULL" : "NULL";
				if($isLast){
					$content = appendCode($content, "$label $custom[datatype] $not_null",1);
				}else{
					$content = appendCode($content, "$label $custom[datatype] $not_null,",1);
				}
			}
		}
		//mysql_free_result($cResult);
		
		//select relationships
		$sql3 = "SELECT component1_id, component2_id, cardinality1, cardinality2, NAME rel_name,connectionType,custom FROM connection WHERE (component1_id=$tRow[id] OR component2_id=$tRow[id]) AND (connectionType='relationship' OR connectionType='iRelationship') AND NOT((cardinality1 REGEXP '[^0-9]+') AND (cardinality2 REGEXP '[^0-9]+'))";
		$rResult = mysql_query($sql3) or die(mysql_error());
		$numOfActualFk = mysql_num_rows($rResult);
		$fkCount = 0;
		
		for($fkIndex = 0; $fkIndex<$numOfActualFk; $fkIndex++){
			$rRow = mysql_fetch_array($rResult);
			if(strcmp($tRow["id"], $rRow["component1_id"])==0 && preg_match("/\D/",$rRow["cardinality1"])==1 && preg_match("/\D/",$rRow["cardinality2"])!=1){
				
				//select the name of table that the foreign key must point to
				$sql4 = "SELECT name FROM component WHERE id = $rRow[component2_id]";
				$rtResult = mysql_query($sql4) or die(mysql_error());
				$rtRow = mysql_fetch_array($rtResult);
				mysql_free_result($rtResult);
				
				//select the primary key(s) of the foreign table
				$sql5 = "SELECT DISTINCT c.id,c.jsonId,c.label,c.custom FROM component c, (SELECT component1_id, component2_id, cardinality1, cardinality2, NAME rel_name,connectionType,custom FROM connection WHERE (component1_id=$rRow[component2_id] OR component2_id=$rRow[component2_id]) AND connectionType='connection') t WHERE (c.id=t.component1_id OR c.id=component2_id) AND NOT(c.id=$rRow[component2_id]) AND c.componentType='kAttribute'";
				$rpkResult = mysql_query($sql5) or die(mysql_error());
				$fpk = array();
				$fpk2 = array();
				$fpk3 = array();
				$i=0;
				
				while($rpkRow = mysql_fetch_array($rpkResult)){
					$fpk[$i] = $rpkRow["label"];
					$fpk2[$i] = $rtRow["name"] . "_".$rpkRow["label"];
					$fpk3[$i] = $rpkRow;
					$i++;
				}
				
				$numPk = count($fpk3);
				for($j=0; $j<$numPk; $j++){
					$currPk = $fpk3[$j];
					$custom = parseCustomAttribute($currPk["custom"]);
					$content = appendCode($content, $rtRow["name"]."_$currPk[label] $custom[datatype] NOT NULL,", 1);
				}
				
				$content = appendCode($content, "CONSTRAINT fk_$tRow[name]_$rtRow[name]", 1);
				$content = appendCode($content, "FOREIGN KEY (". implode(", ", $fpk2) .")", 2);
				$content = appendCode($content, "REFERENCES $rtRow[name] (". implode(",", $fpk) .")", 2);
				$content = appendCode($content, "ON DELETE CASCADE", 2);
				
				if($fkIndex==$numOfActualFk-1){
					$content = appendCode($content, "ON UPDATE NO ACTION", 2);
				}else{
					$content = appendCode($content, "ON UPDATE NO ACTION,", 2);
				}
				mysql_free_result($rpkResult);
				$fkCount++;
			}else if(strcmp($tRow["id"], $rRow["component2_id"])==0 && preg_match("/\D/",$rRow["cardinality2"])==1 && preg_match("/\D/",$rRow["cardinality1"])!=1){
				
				//select the name of table that the foreign key must point to
				$sql4 = "SELECT name FROM component WHERE id = $rRow[component1_id]";
				$rtResult = mysql_query($sql4) or die(mysql_error());
				$rtRow = mysql_fetch_array($rtResult);
				mysql_free_result($rtResult);
				
				//select the primary key(s) of the foreign table
				$sql5 = "SELECT DISTINCT c.id,c.jsonId,c.label,c.custom FROM component c, (SELECT component1_id, component2_id, cardinality1, cardinality2, NAME rel_name,connectionType,custom FROM connection WHERE (component1_id=$rRow[component1_id] OR component2_id=$rRow[component1_id]) AND connectionType='connection') t WHERE (c.id=t.component1_id OR c.id=component2_id) AND NOT(c.id=$rRow[component1_id]) AND c.componentType='kAttribute'";
				$rpkResult = mysql_query($sql5) or die(mysql_error());
				$fpk = array();
				$fpk2 = array();
				$fpk3 = array();
				$i=0;
				
				while($rpkRow = mysql_fetch_array($rpkResult)){
					$fpk[$i] = $rpkRow["label"];
					$fpk2[$i] = $rtRow["name"] . "_".$rpkRow["label"];
					$fpk3[$i] = $rpkRow;
					$i++;
				}
				
				$numPk = count($fpk3);
				for($j=0; $j<$numPk; $j++){
					$currPk = $fpk3[$j];
					$custom = parseCustomAttribute($currPk["custom"]);
					$content = appendCode($content, $rtRow["name"]."_$currPk[label] $custom[datatype] NOT NULL,", 1);
				}
				
				$content = appendCode($content, "CONSTRAINT fk_$tRow[name]_$rtRow[name]", 1);
				$content = appendCode($content, "FOREIGN KEY (". implode(", ", $fpk2) .")", 2);
				$content = appendCode($content, "REFERENCES $rtRow[name] (". implode(",", $fpk) .")", 2);
				$content = appendCode($content, "ON DELETE CASCADE", 2);
				if($fkIndex==$numOfActualFk-1){
					$content = appendCode($content, "ON UPDATE NO ACTION", 2);
				}else{
					$content = appendCode($content, "ON UPDATE NO ACTION,", 2);
				}
				
				mysql_free_result($rpkResult);
				$fkCount++;
			}else if(preg_match("/\D/",$rRow["cardinality2"])==1 && preg_match("/\D/",$rRow["cardinality1"])==1){
				//many to many relationship
			}
		}
		mysql_free_result($rResult);
		
		if($fkCount>0){
			$content = appendCode($content, ",PRIMARY KEY(". implode(",", $pk) .") ", 1);
		}else{
			$content = appendCode($content, "PRIMARY KEY(". implode(",", $pk) .") ", 1);
		}
		
		$content = appendCode($content, ")ENGINE = InnoDB;");
	}
	mysql_free_result($tResult);
	
	//select tables to be created (multi valued attributes only)
	$sql1 = "SELECT * FROM component WHERE componentType='mAttribute' AND diagram_id=$dRow[id]";
	$tResult = mysql_query($sql1) or die(mysql_error());
	while($tRow = mysql_fetch_array($tResult)){
		$content = appendCode($content, "\nDROP TABLE IF EXISTS $tRow[name];");
		$content = appendCode($content, "CREATE  TABLE IF NOT EXISTS $tRow[name](");
		
		$custom = parseCustomAttribute($tRow["custom"]);
		
		$content = appendCode($content, "Id INT NOT NULL AUTO_INCREMENT,",1);
		$content = appendCode($content, "$tRow[name] $custom[datatype] NOT NULL,",1);
		
		//select the parent table
		$sql2 = "SELECT DISTINCT c.id,c.jsonId,c.label FROM component c, (SELECT component1_id, component2_id, cardinality1, cardinality2, NAME rel_name,connectionType,custom FROM connection WHERE (component1_id=$tRow[id] OR component2_id=$tRow[id]) AND connectionType='connection') t WHERE (c.id=t.component1_id OR c.id=component2_id) AND NOT(c.id=$tRow[id]) AND (componentType='entity' OR componentType='wEntity')";
		$rResult = mysql_query($sql2) or die(mysql_error());
		$rRow = mysql_fetch_array($rResult);
		mysql_free_result($rResult);
		
		//select the primary key(s)
		$sql3 = "SELECT DISTINCT c.id,c.jsonId,c.label,c.custom FROM component c, (SELECT component1_id, component2_id, cardinality1, cardinality2, NAME rel_name,connectionType,custom FROM connection WHERE (component1_id=$rRow[id] OR component2_id=$rRow[id]) AND connectionType='connection') t WHERE (c.id=t.component1_id OR c.id=component2_id) AND NOT(c.id=$rRow[id]) AND c.componentType='kAttribute'";
		$rpkResult = mysql_query($sql3) or die(mysql_error());
		$fpk = array();
		$fpk2 = array();
		$fpk3 = array();
		$i=0;
		
		while($rpkRow = mysql_fetch_array($rpkResult)){
			$fpk[$i] = $rpkRow["label"];
			$fpk2[$i] = $rRow["label"] . "_".$rpkRow["label"];
			$fpk3[$i] = $rpkRow;
			$i++;
		}
		
		$numPk = count($fpk3);
		for($j=0; $j<$numPk; $j++){
			$currPk = $fpk3[$j];
			$custom = parseCustomAttribute($currPk["custom"]);
			$content = appendCode($content, $rRow["label"]."_$currPk[label] $custom[datatype] NOT NULL,", 1);
		}
		
		$content = appendCode($content, "CONSTRAINT fk_$tRow[name]_$rRow[label]", 1);
		$content = appendCode($content, "FOREIGN KEY (". implode(", ", $fpk2) .")", 2);
		$content = appendCode($content, "REFERENCES $rRow[label] (". implode(",", $fpk) .")", 2);
		$content = appendCode($content, "ON DELETE CASCADE", 2);
		$content = appendCode($content, "ON UPDATE NO ACTION", 2);
		mysql_free_result($rpkResult);
		
		$content = appendCode($content, ",PRIMARY KEY(Id) ", 1);
		$content = appendCode($content, ")ENGINE = InnoDB;");
	}
	mysql_free_result($tResult);
	
	//select the connections that represent the many to many relationship
	$sql1 = "SELECT DISTINCT c.component1_id,c.component2_id, c.jsonId,c.name,c.component1_dir,c.component2_dir,connectionType,cardinality1,cardinality2,component1_jsonId,component2_jsonId,c.stereotype,c.description,c.custom FROM connection c,component co,diagram d WHERE (cardinality1 REGEXP '[^0-9]+') AND (cardinality2 REGEXP '[^0-9]+') AND (co.id=c.component1_id OR co.id=c.component2_id) AND co.diagram_id=$dRow[id]";
	$cResult = mysql_query($sql1) or die(mysql_error());
	$tableNum = 0;
	while($cRow = mysql_fetch_array($cResult)){
		$content = appendCode($content, "\nDROP TABLE IF EXISTS $cRow[stereotype]_$tableNum;");
		$content = appendCode($content, "CREATE  TABLE IF NOT EXISTS $cRow[stereotype]_$tableNum(");
		
		$tableNum++;
		$tableNames = array();
		$pk = array();
		$pk2 = array();
		
		//select tables connected by the many to many relationship
		$sql2 = "SELECT id,name FROM component WHERE id=$cRow[component1_id] OR id=$cRow[component2_id] AND (componentType='entity' OR componenttype='wEntity')";
		$tResult = mysql_query($sql2) or die(mysql_error());
		$i=0;
		while($tRow= mysql_fetch_array($tResult)){
			$tableNames[$i++] = $tRow["name"];
			
			//select the primary keys
			$sql3 = "SELECT DISTINCT c.id,c.jsonId,c.label,c.custom FROM component c, (SELECT component1_id, component2_id, cardinality1, cardinality2, NAME rel_name,connectionType,custom FROM connection WHERE (component1_id=$tRow[id] OR component2_id=$tRow[id]) AND connectionType='connection') t WHERE (c.id=t.component1_id OR c.id=component2_id) AND NOT(c.id=$tRow[id]) AND c.componentType='kAttribute'";
			$pkResult = mysql_query($sql3) or die(mysql_error());
			$j=0;
			while($pkRow = mysql_fetch_assoc($pkResult)){
				$pk[$tRow["name"]][$j++] = $pkRow;
			}
			mysql_free_result($pkResult);
		}
		mysql_free_result($tResult);
		
		//fields
		$numNames = count($tableNames);
		for($i=0,$k=0;$i<$numNames; $i++){
			$currName = $tableNames[$i];
			$numPk = count($pk[$currName]);
			for($j=0; $j<$numPk; $j++,$k++){
				$currPk = $pk[$currName][$j];
				$custom = parseCustomAttribute($currPk["custom"]);
				
				$pk2[$k] = $currName."_$currPk[label]";
				$content = appendCode($content, $currName."_$currPk[label] $custom[datatype] NOT NULL,", 1);
			}
		}
		
		$content = appendCode($content, "PRIMARY KEY(" . implode(", ", $pk2) . ") ,", 1);
		
		//foreign keys
		$numNames = count($tableNames);
		for($i=0,$k=0;$i<$numNames; $i++){
			$currName = $tableNames[$i];
			$content = appendCode($content, "CONSTRAINT fk_$cRow[stereotype]_$currName", 1);
			$numPk = count($pk[$currName]);
			$pk3 = array();
			$pk4 = array();
			for($j=0; $j<$numPk; $j++,$k++){
				$currPk = $pk[$currName][$j];
				$pk3[$k] = $currPk["label"];
				$pk4[$k] = $currName."_$currPk[label]";
			}
			$content = appendCode($content, "FOREIGN KEY (". implode(", ", $pk4) .")", 2);
			$content = appendCode($content, "REFERENCES $currName (". implode(",", $pk3) .")", 2);
			
			$content = appendCode($content, "ON DELETE CASCADE", 2);
			if($i==$numNames-1){
				$content = appendCode($content, "ON UPDATE NO ACTION", 2);
			}else{
				$content = appendCode($content, "ON UPDATE NO ACTION,", 2);
			}
		}
		
		$content = appendCode($content, ")ENGINE = InnoDB;");
	}
	mysql_free_result($cResult);
	
	$filePath = "sql_$_SESSION[username]_project$_GET[id]_$_GET[jsonId].sql";
	
	header("Content-length: ". mb_strlen($content));
	header("Content-type: text/plain");
	header("Content-Disposition: attachment; filename=$filePath");
	echo $content;
	exit;
?>